#!/usr/local/bin/perl -w

use App::Options (
    options => [qw(dbdriver dbclass dbhost dbname dbuser dbpass)],
    option => {
        dbclass  => { default => "App::Repository::MySQL", },
        dbdriver => { default => "mysql", },
        dbhost   => { default => "localhost", },
        dbname   => { default => "test", },
        dbuser   => { default => "", },
        dbpass   => { default => "", },
    },
);

use Test::More qw(no_plan);
use lib "../App-Context/lib";
use lib "../../App-Context/lib";
use lib "lib";
use lib "../lib";
use lib ".";
use lib "t";

use_ok("App");
use_ok("App::Repository");
use RepositoryTestUtils qw(create_table_test_person drop_table_test_person populate_table_test_person);
use strict;

if (!$App::options{dbuser}) {
    ok(1, "No dbuser given. Tests assumed OK. (add dbuser=xxx and dbpass=yyy to app.conf in 't' directory)");
    exit(0);
}

my $context = App->context(
    conf_file => "",
    conf => {
        Repository => {
            default => {
                class => $App::options{dbclass},
                dbdriver => $App::options{dbdriver},
                dbhost => $App::options{dbhost},
                dbname => $App::options{dbname},
                dbuser => $App::options{dbuser},
                dbpass => $App::options{dbpass},
                table => {
                    test_person => {
                        primary_key => ["person_id"],
                    },
                },
            },
        },
    },
    debug_sql => $App::options{debug_sql},
    trace => $App::options{trace},
);

my $db = $context->repository();
&drop_table_test_person($db);
&create_table_test_person($db);

{
    $db->_load_rep_metadata();

    my $dbtype = $App::options{dbtype};
    if ($dbtype ne "oracle") {
        ok(1, "These tests are only for Oracle");
    }
    else {
        my ($id, $nrows);
        my ($expect_sql, $sql);
        ok($db->_insert_row("test_person", ["age","first_name","gender","state"],
            [39,"stephen",  "M","GA"]),
            "insert row (primary key included)");
        ok($db->_insert_row("test_person", ["age","first_name","gender","state"],
            [37,"susan",    "F","GA"]),
            "insert row (primary key excluded, auto_increment)");
        ok($db->_insert_row("test_person", ["age","first_name","gender","state"],
            [6,"maryalice","F","GA"]),
            "insert row (primary key included, null)");
        ok($db->_insert_row("test_person", ["age","first_name","gender","state"],
            [3,"paul",     "M","GA"]),
            "insert row (primary key included, 0)");

        $expect_sql = <<EOF;
insert into test_person
  (age,
   first_name,
   gender,
   state)
values
  (?,
   ?,
   ?,
   ?)
EOF
        ok($db->_insert_row("test_person", ["age","first_name","gender","state"],
            { age => 1, first_name => "christine", gender => "F", state => "GA" }),
            "insert again (cols, values as hash)");
        is($db->{sql}, $expect_sql, "insert row. sql ok");
        $expect_sql .= " returning person_id into ?\n";

        $nrows = $db->_insert_row("test_person", ["age","first_name","gender","state"],
            { age => 45, first_name => "tim", gender => "M", state => "GA" }, {last_inserted_id => 1});
        ok($nrows, "insert again (cols, values as hash, returning last_inserted_id)");
        $id = $db->_last_inserted_id("test_person");
        is($id, 6, "insert again (cols, values as hash, returning last_inserted_id) got id=6");
        is($db->{sql}, $expect_sql, "insert again (cols, values as hash, returning last_inserted_id) sql ok");

        $nrows = $db->_insert_row("test_person",
            { age => 39, first_name => "keith", gender => "M", state => "GA" }, undef, {last_inserted_id => 1}),
        ok($nrows, "insert again (cols/values as single hash, returning last_inserted_id)");
        $id = $db->_last_inserted_id("test_person");
        is($id, 7, "insert again (cols/values as single hash, returning last_inserted_id) got id=7");

        ok($db->insert("test_person", {
                # person_id => 8,
                age => 35,
                first_name => "alex",
                gender => "M",
                state => "GA",
            }),
            "insert hash");
        eval {
            $db->insert_row("test_person", {
                person_id => 8,
                age => 35,
                first_name => "alex2",
                gender => "M",
                state => "GA",
            });
        };
        ok($@, "insert dup hash fails");
        ok($db->insert("test_person", undef, {
                # person_id => 9,
                age => 35,
                first_name => "alex3",
                gender => "M",
                state => "GA",
            }),
            "insert hash in 2nd pos");
        ok($db->insert("test_person", ["age","first_name","gender","state"], {
                # person_id => 10,
                age => 35,
                first_name => "alex4",
                gender => "M",
                state => "GA",
            }),
            "insert hash in 2nd pos w/ col spec");
        eval {
            $db->insert_row("test_person", undef, {
                person_id => 10,
                age => 35,
                first_name => "alex",
                gender => "M",
                state => "GA",
            });
        };
        ok($@, "insert dup hash in 2nd pos fails");

        ok($db->insert("test_person", undef, {
                # person_id => 11,
                age => 999,
                first_name => '%@$\\\'',
                gender => "M",
                state => "GA",
            }),
            "insert \\ and ' and \\' seems to work");
        is($db->get("test_person",11,"first_name"),'%@$\\\'', "yep. putting weird chars in first_name worked.");

        my $new_hashes =
           [{ age=>39, first_name=>"stephen", gender=>"M", state=>"GA", foo=>"bar"},
            { age=>37, first_name=>"susan", gender=>"F", state=>"GA", foo=>"bar"},
            { age=>6, first_name=>"maryalice", gender=>"F", state=>"GA", foo=>"bar"},
            { age=>3, first_name=>"paul", gender=>"M", state=>"GA", foo=>"bar"},
            { age=>1, first_name=>"christine", gender=>"F", state=>"GA", foo=>"bar"},
            { age=>45, first_name=>"tim", gender=>"M", state=>"GA", foo=>"bar"},
            { age=>39, first_name=>"keith", gender=>"M", state=>"GA", foo=>"bar"},];

        my $new_rows =
           [[39,"stephen",  "M","CA"],
            [37,"susan",    "F","CA"],
            [6,"maryalice", "F","CA"],
            [3,"paul",      "M","CA"],
            [1,"christine", "F","CA"],
            [45,"tim",      "M","CA"],
            [39,"keith",    "M","CA"],];

        my $dup_rows =
           [[1, 39,"stephen",  "M","GA"],
            [2, 37,"susan",    "F","GA"],
            [3, 6,"maryalice", "F","GA"],
            [4, 3,"paul",      "M","GA"],
            [5, 1,"christine", "F","GA"],
            [6, 45,"tim",      "M","GA"],
            [7, 39,"keith",    "M","GA"],];

#        $expect_sql = <<EOF;
#insert into test_person
#  (age, first_name, gender, state)
#values
#  (39, 'stephen', 'M', 'GA'),
#  (37, 'susan', 'F', 'GA'),
#  (6, 'maryalice', 'F', 'GA'),
#  (3, 'paul', 'M', 'GA'),
#  (1, 'christine', 'F', 'GA'),
#  (45, 'tim', 'M', 'GA'),
#  (39, 'keith', 'M', 'GA')
#EOF
#        $sql = $db->_mk_insert_rows_sql("test_person", ["age","first_name","gender","state"], $new_rows);
#        is($sql, $expect_sql, "_mk_insert_rows_sql(): 7 rows, bulk insert");
#        $sql = $db->_mk_insert_rows_sql("test_person", ["age","first_name","gender","state"], $new_hashes);
#        is($sql, $expect_sql, "_mk_insert_rows_sql(): 7 rows, bulk insert (from hashes)");

#        $expect_sql = <<EOF;
#replace into test_person
#  (age, first_name, gender, state)
#values
#  (39, 'stephen', 'M', 'GA'),
#  (37, 'susan', 'F', 'GA'),
#  (6, 'maryalice', 'F', 'GA'),
#  (3, 'paul', 'M', 'GA'),
#  (1, 'christine', 'F', 'GA'),
#  (45, 'tim', 'M', 'GA'),
#  (39, 'keith', 'M', 'GA')
#EOF
#        $sql = $db->_mk_insert_rows_sql("test_person", ["age","first_name","gender","state"], $new_rows, { replace => 1 });
#        is($sql, $expect_sql, "_mk_insert_rows_sql(): 7 rows, bulk replace");

#        $expect_sql = <<EOF;
#insert into test_person
#  (person_id, age, first_name, gender, state)
#values
#  (1, 39, 'stephen', 'M', 'GA'),
#  (2, 37, 'susan', 'F', 'GA'),
#  (3, 6, 'maryalice', 'F', 'GA'),
#  (4, 3, 'paul', 'M', 'GA'),
#  (5, 1, 'christine', 'F', 'GA'),
#  (6, 45, 'tim', 'M', 'GA'),
#  (7, 39, 'keith', 'M', 'GA')
#on duplicate key update
#   person_id = values(person_id),
#   age = values(age),
#   first_name = values(first_name),
#   gender = values(gender),
#   state = values(state)
#EOF
#        $sql = $db->_mk_insert_rows_sql("test_person", ["person_id", "age","first_name","gender","state"], $dup_rows, { update => 1 });
#        is($sql, $expect_sql, "_mk_insert_rows_sql(): 7 rows, bulk insert/update");

        #######################################
        $nrows = $db->insert_rows("test_person", ["age","first_name","gender","state"], $new_rows);
        is($nrows, 7, "insert_rows(): 7 rows, bulk insert");
        #$nrows = $db->insert_rows("test_person", ["person_id","age","first_name","gender","state"], $dup_rows, { replace => 1 });
        #is($nrows, 7, "insert_rows(): 7 rows, bulk replace");

        $nrows = $db->insert_rows("test_person", ["person_id", "age","first_name","gender","state"], $dup_rows, { update => 1 });
        is($nrows, 7, "insert_rows(): 7 rows, bulk insert/update");

        #$nrows = $db->insert_rows("test_person", ["person_id","age","first_name","gender","state"], $dup_rows, { replace => 1, maxrows => 4 });
        #is($nrows, 7, "insert_rows(): 7 rows, bulk replace (4 at a time)");
        #$nrows = $db->insert_rows("test_person", ["person_id", "age","first_name","gender","state"], $dup_rows, { update => 1, maxrows => 4 });
        #is($nrows, 7, "insert_rows(): 7 rows, bulk insert/update (4 at a time)");

        $nrows = $db->insert_rows("test_person", [{first_name => "Steve", state => "GA", gender => "M", age => 47 }], [], {update => 1});
        is($nrows, 1, "insert_rows(): 1 rows, array of hashes");
    }
}

exit 0;

